package com.jdbc;

import com.mchange.v2.c3p0.ComboPooledDataSource;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.*;
import javax.sql.DataSource;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class DBUtil {
    private DBUtil() {
    }
    private static DataSource dataSource;
    static {
        dataSource = new ComboPooledDataSource("mysql");
    }
    public static Connection getConn() {
        Connection conn = null;
        try {
            conn = dataSource.getConnection();
        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        }
        return conn;
    }
    public static void close(Connection conn) {
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
    public static void close(ResultSet rs, Statement stm, Connection conn) {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        if (stm != null) {
            try {
                stm.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        if (conn != null) {
            try {
                conn.close(); // 不会真的销毁连接,会把连接放回连接池中,因为经过DataSource得到的连接,其实经过了代理, close() 已经在内部重写了
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
    //万能更新
    public static int update(String sql,Object...param){
        int result=0;
        Connection conn=null;

        try {
            conn=getConn();
            result=new QueryRunner().update(conn,sql,param);

        }catch (Exception e)
        {
            e.printStackTrace();
            throw new RuntimeException();
        }finally {
            close(conn);
        }
        return result;
    }
    public static int returnId(String sql,Object...param){
        int autoId=0;
        Connection conn=null;
        PreparedStatement stm=null;
        try {
            conn=getConn();
            stm=conn.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
            for(int i=0;i<param.length;i++) {
                stm.setObject(i+1, param[i]);
            }
            stm.execute();
            ResultSet rskey=stm.getGeneratedKeys();
            rskey.next();
            autoId=rskey.getInt(1);

        }catch (Exception e){
            e.printStackTrace();
            throw new RuntimeException();
        }
        finally {
            close(conn);
        }
        return autoId;
    }
    //查询单个对象
    public static <T> T getSingleObject(String sql,Class<T> clazz,Object ...  params) {
        T result=null;
        Connection conn=null;
        try {
            conn=getConn();
            result=new QueryRunner().query(conn,sql, new BeanHandler<T>(clazz),params);

        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        }finally {
            close(conn);
        }

        return result;
    }
    //查询多个对象
    public static <T> List<T> getList(String sql, Class<T> clazz, Object ...  params){
        List<T> list=new ArrayList<>();
        Connection conn=null;
        try {
            conn=getConn();
            list=new QueryRunner().query(conn,sql, new BeanListHandler<T>(clazz),params);

        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        }finally {
            close(conn);
        }

        return list;
    }
    //返回Map集合
    public static Map<String,Object> getMap(String sql, Object ...params){
        Map<String,Object> m=new HashMap<String,Object>();
        Connection conn=null;
        try {
            conn=getConn();
            m=new QueryRunner().query(conn,sql, new MapHandler(), params);

        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        }finally {
            close(conn);
        }
        return m;
    }
    //返回一个List集合,里面的每条数据都是一个Map集合
    public static List<Map<String,Object>> getMapList(String sql,Object ... params){
        List<Map<String,Object>> listMap=new ArrayList<Map<String,Object>> ();
        Connection conn=null;
        try {
            conn=getConn();
            listMap=new QueryRunner().query(conn,sql, new MapListHandler(), params);

        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        }finally {
            close(conn);
        }

        return listMap;
    }
    //返回单个数据
    public static <T> T getScalar(String sql, Object ... params) {
        T result=null;
        Connection conn=null;
        try {
            conn=DBUtil.getConn();
            result=new QueryRunner().query(conn,sql,new ScalarHandler<T>(1), params);
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        }finally {
            close(conn);
        }
        return result;
    }
    //返回单个数据的list
    public static <T> List<T>  getList(String sql, Object ... params) {
        List<T> list = new ArrayList<>();
        Connection conn = null;
        try {
            conn = DBUtil.getConn();
            list = new QueryRunner().query(conn, sql, new ColumnListHandler<T>(1), params);

        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        } finally {
            close(conn);
        }
        return list;
    }
}
